<?php
use Carbon\Carbon;
class OrderComponent extends Eloquent {
	
	protected $table = 'orders_components';
	
	public function component() {
		return $this->belongsTo('Component');
	}
	
	public function order() {
		return $this->belongsTo('Order');
	}
	
	public function logs() {
		return $this->hasMany('OrderComponentLog');
	}
	
	public function costsCentres() {
		return $this->belongsToMany('CostsCentre', 'production_steps');
	}
	
	public function hasCurrentCostsCentrePrivilege() {
		if (Auth::user()->hasPrivilege('admin')) {
			return true;
		}
		
		$currentCostsCentreId = $this->costsCentres()->where('step', '=', $this->current_step)->first()->id;
		return Auth::user()->hasPrivilege('cc_' . $currentCostsCentreId);
	}
	
	public static function getLateComponents(array $options, $offset, $limit) {
		return OrderComponent::getComponents($options, 'late', $offset, $limit, true);
	}
	
	public static function getLateOrders(array $options, $offset, $limit) {
		return OrderComponent::getOrders($options, 'late', $offset, $limit);
	}
	
	public static function getOnScheduleComponents(array $options, $offset, $limit) {
		return OrderComponent::getComponents($options, 'onSchedule', $offset, $limit);
	}
	
	public static function getOnScheduleOrders(array $options, $offset, $limit) {
		return OrderComponent::getOrders($options, 'onSchedule', $offset, $limit);
	}
	
	public static function getAheadScheduleComponents(array $options, $offset, $limit) {
		return OrderComponent::getComponents($options, 'aheadSchedule', $offset, $limit, true);
	}
	
	public static function getAheadScheduleOrders(array $options, $offset, $limit) {
		return OrderComponent::getOrders($options, 'aheadSchedule', $offset, $limit);
	}
	
	public static function getComponents(array $options, $type, $offset, $limit, $getProbableCcName = false) {
		$where = array();
		$bindings = array();
		$refDate = "current_date()";
		
		if (!empty($options['refDate'])) {
			$refDate = ":refDate";
			$bindings['refDate'] = Carbon::createFromFormat('d/m/Y', $options['refDate'])->format('Y-m-d');
		}
		
		if (!empty($options['orderNumber'])) {
			$where[] = "order_number = :orderNumber";
			$bindings['orderNumber'] = $options['orderNumber'];
		}
		
		if (!empty($options['productionNumber'])) {
			$where[] = "production_number = :productionNumber";
			$bindings['productionNumber'] = $options['productionNumber'];
		}
		
		if (!empty($options['customerId'])) {
			$where[] = "customer_id = :customerId";
			$bindings['customerId'] = $options['customerId'];
		}
		
		if (!empty($options['costsCentresId'])) {
			$where[] = "actual_costs_centre_id = :costsCentresId";
			$bindings['costsCentresId'] = $options['costsCentresId'];
		}
		
		if ($options['accessory'] !== '') {
			$where[] = "accessory = :accessory";
			$bindings['accessory'] = $options['accessory'];
		}
		
		if ($options['code'] !== '') {
			$where[] = "code = :code";
			$bindings['code'] = $options['code'];
		}
		
		switch($type) {
			case 'late':
				$where[] = "probable_date < {$refDate}";
				break;
			case 'onSchedule':
				if (!empty($options['refDate'])) {
					$where[] = "(:refDatePrevious >= previous_probable_date AND :refDateCurrent <= probable_date)";
					// nao aceita o mesmo nome de parametro duas vezes
					$bindings['refDatePrevious'] = $bindings['refDate'];
					$bindings['refDateCurrent'] = $bindings['refDate'];
					unset($bindings['refDate']);
				} else {
					$where[] = "(current_date() >= previous_probable_date AND current_date() <= probable_date)";
				}
				break;
			case 'aheadSchedule':
				$where[] = "previous_probable_date > {$refDate}";
				break;
		}
		
		$results = OrderComponent::report(join(" AND ", $where), $bindings, $offset, $limit);
		
		if ($getProbableCcName) {
			$refDateBinding = array();
			if (!empty($options['refDate'])) {
				$refDateBinding['refDate'] = $bindings['refDate'];
			}
			
			foreach ($results['data'] as $key => $item) {
				$probableCC = OrderComponent::getProbableCostsCentre($item->order_component_id, $refDate, $refDateBinding);
				
				if (!empty($probableCC)) {
					$probableCCName = $probableCC->cc_name;
				} else {
					$probableCCName = "Concluído";
				}
			
				$results['data'][$key] = (object) array_merge((array) $results['data'][$key], array( 'probable_cc_name' => $probableCCName));
			}
		}
		
		return $results;
	}
	
	public static function getOrders(array $options, $type, $offset, $limit) {
		$where = array();
		$bindings = array();
		$refDate = "current_date()";
	
		if (!empty($options['refDate'])) {
			$refDate = ":refDate";
			$bindings['refDate'] = Carbon::createFromFormat('d/m/Y', $options['refDate'])->format('Y-m-d');
		}
	
		if (!empty($options['orderNumber'])) {
			$where[] = "order_number = :orderNumber";
			$bindings['orderNumber'] = $options['orderNumber'];
		}
		
		if (!empty($options['productionNumber'])) {
			$where[] = "production_number = :productionNumber";
			$bindings['productionNumber'] = $options['productionNumber'];
		}
	
		if (!empty($options['customerId'])) {
			$where[] = "customer_id = :customerId";
			$bindings['customerId'] = $options['customerId'];
		}
	
		switch($type) {
			case 'late':
				$where[] = "min_probable_date < {$refDate}";
				break;
			case 'onSchedule':
				if (!empty($options['refDate'])) {
					$where[] = "(:refDatePrevious >= min_previous_probable_date AND :refDateCurrent <= min_probable_date)";
					// nao aceita o mesmo nome de parametro duas vezes
					$bindings['refDatePrevious'] = $bindings['refDate'];
					$bindings['refDateCurrent'] = $bindings['refDate'];
					unset($bindings['refDate']);
				} else {
					$where[] = "(current_date() >= min_previous_probable_date AND current_date() <= min_probable_date)";
				}
				break;
			case 'aheadSchedule':
				$where[] = "min_previous_probable_date > {$refDate}";
				break;
		}
	
		return OrderComponent::report(join(" AND ", $where), $bindings, $offset, $limit, true);
	}
	
	public static function whereOrderWithComponents($orderId) {
		return OrderComponent::where('order_id', '=', $orderId)->with(array(
			'costsCentres' => function($query) {
				$query->orderBy('step', 'asc');
			},
			'component' => function($query) {
				$query->orderBy('code', 'asc')->orderBy('name', 'asc');
			}
		));
	}
	
	public static function report($where, array $bindings, $offset, $limit, $byOrder = false, $excludeFinished = true) {
		$baseRefDate = 'current_date()';
		if (isset($bindings['refDate'])) {
			$baseRefDate = ':baseRefDate';
			$bindings['baseRefDate'] = $bindings['refDate'];
		}
		
		if ($excludeFinished) {
			$excludeFinishedStr = " AND oc.current_step <> 99 ";
		} else {
			$excludeFinishedStr = "";
		}
		
		$subQuery = "
			SELECT
				oc.id AS order_component_id
				, c.id AS component_id
				, c.name AS component_name
				, c.accessory
				, ccActual.id AS actual_costs_centre_id
				, ccActual.name AS actual_costs_centre_name
				, CASE
					WHEN oc.current_step = 1 THEN
						o.production_start_date
					WHEN (COUNT(ps.order_component_id)  - (oc.current_step - 1)) < (DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) * COUNT(ps.order_component_id)) THEN
						DATE_ADD(
							o.production_start_date,
							INTERVAL ((oc.current_step - 1) * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) + ((DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) * COUNT(ps.order_component_id)) - (COUNT(ps.order_component_id)  - (oc.current_step - 1)))) DAY
						)
					ELSE				
						DATE_ADD(
							o.production_start_date,
							INTERVAL ((oc.current_step - 1) * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id)))) DAY
						)
				END AS previous_probable_date
				, CASE
					WHEN (COUNT(ps.order_component_id)  - oc.current_step) < (DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) * COUNT(ps.order_component_id)) THEN
					DATE_ADD(
					o.production_start_date,
					INTERVAL (oc.current_step * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) + ((DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id))) * COUNT(ps.order_component_id)) - (COUNT(ps.order_component_id)  - oc.current_step))) - 1 DAY
					)
				ELSE
					DATE_ADD(
					o.production_start_date,
					INTERVAL (oc.current_step * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / COUNT(ps.order_component_id)))) - 1 DAY
					)
				END AS probable_date
				, o.order_number
				, o.production_number
				, o.customer_id
				, o.production_start_date
				, o.production_end_date
				, o.id AS order_id
				, oc.current_step
				, cus.name AS customer_name
                , c.code
                , oc.amount AS component_amount
                , o.amount AS order_amount
			FROM
				orders_components oc
				INNER JOIN orders o ON (oc.order_id = o.id)
				INNER JOIN customers cus ON (o.customer_id = cus.id)
				INNER JOIN production_steps ps ON (oc.id = ps.order_component_id)
				INNER JOIN components c ON (oc.component_id = c.id)
				LEFT JOIN production_steps psActual ON (oc.id = psActual.order_component_id AND psActual.step = oc.current_step)
				LEFT JOIN costs_centres ccActual ON (psActual.costs_centre_id = ccActual.id)
			WHERE
				o.production_start_date <= {$baseRefDate}
				${excludeFinishedStr}
			GROUP BY
				oc.id	
		";
		
		if ($byOrder) {
			$results = DB::select(DB::raw("
				SELECT
					SQL_CALC_FOUND_ROWS
					*
				FROM (
					SELECT 
						componentsWithDates.*
						, MIN(previous_probable_date) AS min_previous_probable_date
						, MIN(probable_date) AS min_probable_date
					FROM (
						${subQuery}
					) AS componentsWithDates
					GROUP BY
						order_id
				) ordersWithDates
				WHERE
					${where}
				ORDER BY
					order_number
				LIMIT
					:limit
				OFFSET
					:offset
			"), 
			array_merge(
				$bindings, 
				array(
					'limit' => $limit,
					'offset' => $offset
				)
			));
		} else {
			$results = DB::select(DB::raw("
				SELECT 
					SQL_CALC_FOUND_ROWS
					*
				FROM (
					${subQuery}
				) AS componentsWithDates
				WHERE
					${where}
				ORDER BY
					probable_date, component_name
				LIMIT
					:limit
				OFFSET
					:offset
			"), 
			array_merge(
				$bindings, 
				array(
					'limit' => $limit,
					'offset' => $offset
				)
			));
		}
		
		$totalRows = DB::selectOne('SELECT FOUND_ROWS() AS total');
		
		return array(
			'total' => $totalRows->total,
			'data' => $results
		);
	}
	
	public static function getComponentsByOrder($orderId) {
		// TODO: remover limit
		$results = OrderComponent::report("order_id = :orderId", array('orderId' => $orderId), 0, 999, false, false);
		$refDate = Carbon::now();
		
		foreach ($results['data'] as $key => $item) {
			$status = null;
			$probableDate = Carbon::createFromFormat("Y-m-d", $item->probable_date);
			$previousProbableDate = Carbon::createFromFormat("Y-m-d", $item->previous_probable_date);
			
			if ($item->current_step == 99) {
				$status = "finished";
			} elseif ($probableDate->lt($refDate)) {
				$status = "late";
			} elseif ($refDate->gte($previousProbableDate) && $refDate->lte($probableDate)) {
				$status = "onSchedule";
			} elseif ($probableDate->gt($refDate)) {
				$status = "aheadSchedule";
			} 
			
			$results['data'][$key] = (object) array_merge((array) $results['data'][$key], array('status' => $status));
		}
		
		return $results['data'];
	}
	
	public static function getProbableCostsCentre($orderComponentId, $refDate, array $bindings) {
		$probableCostsCentreQuery = OrderComponent::getProbableCostsCentreQuery();
		return DB::selectOne("
			SELECT
				*
			FROM (
				{$probableCostsCentreQuery}
			) AS cc_dates
			WHERE
				probable_date >= {$refDate}
			ORDER BY
				probable_date ASC
			LIMIT 1
		", array_merge(array('orderComponentId' => $orderComponentId), $bindings)
		);
	}
	
	public static function getCostsCentresWithProbableDates($orderComponentId) {
		$query = OrderComponent::getProbableCostsCentreQuery();
		
		return DB::select("
				SELECT
					*
					, (probable_date < current_date()) AS is_late
				FROM (
					{$query}
				) AS cc_dates
				ORDER BY
					probable_date ASC
			", array('orderComponentId' => $orderComponentId)
		);
	}
	
	private static function getProbableCostsCentreQuery() {
		return "
			SELECT
				CASE	
					WHEN ps.step = 1 THEN
						o.production_start_date
					WHEN (total_cc  - (ps.step - 1)) < (DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) * total_cc) THEN
						DATE_ADD(
							o.production_start_date,
							INTERVAL ((ps.step - 1) * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) + ((DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) * total_cc) - (total_cc  - (ps.step - 1))))  DAY
						)
					ELSE				
						DATE_ADD(
							o.production_start_date,
							INTERVAL ((ps.step - 1) * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc))) DAY
						)
				END AS previous_probable_date
				, CASE
					WHEN (total_cc - ps.step) < (DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) * total_cc) THEN
						DATE_ADD(
							o.production_start_date,
							INTERVAL (ps.step * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) + ((DATEDIFF(o.production_end_date, o.production_start_date) + 1) - (FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc)) * total_cc) - (total_cc  - ps.step))) - 1 DAY
						)
					ELSE				
						DATE_ADD(
							o.production_start_date,
							INTERVAL (ps.step * FLOOR(((DATEDIFF(o.production_end_date, o.production_start_date) + 1) / total_cc))) - 1 DAY
						)
				END AS probable_date
				, oc.id AS order_component_id
				, cc.id AS costs_centre_id
				, cc.name AS cc_name
				, log.created_at AS log_created_at
				, u.username AS log_username
			FROM (
				SELECT
					oc.id
					, COUNT(ps.order_component_id) AS total_cc
				FROM
					orders_components oc
					INNER JOIN orders o ON (oc.order_id = o.id)
					INNER JOIN production_steps ps ON (oc.id = ps.order_component_id)
				WHERE
					oc.id = :orderComponentId
				GROUP BY
					oc.id
			) orders_components_with_cc_count
				INNER JOIN orders_components oc ON (orders_components_with_cc_count.id = oc.id)
				INNER JOIN production_steps ps ON (oc.id = ps.order_component_id)
				INNER JOIN orders o ON (oc.order_id = o.id)
				INNER JOIN costs_centres cc ON (ps.costs_centre_id = cc.id)
				LEFT JOIN order_component_logs log ON (log.id = (
					SELECT
						MAX(l.id)
					FROM
						order_component_logs l
					WHERE
						l.order_component_id = oc.id
						AND l.step = ps.step
				))
				LEFT JOIN users u ON (log.user_id = u.id)
		";
	}
	
	public function updateStep($step) {
		DB::transaction(function() use($step) {
			$current_step = $this->current_step;
			
			if ($this->hasCurrentCostsCentrePrivilege()) {
				$this->current_step = $step;
				$this->save();
				
				if ($step === 99) {
					$this->log($current_step);
				} else {
					if ($current_step > $step) {
						for ($s = $current_step; $s > $step; $s--) {
							$this->log($s);
						}
					} else {
						for ($s = $current_step; $s < $step; $s++) {
							$this->log($s);
						}
					}
				}
			}
		});
	}
	
	public function advanceStep() {
		$this->updateStep($this->current_step + 1);
	}
	
	public function markComplete() {
		$this->updateStep(99);
	}
	
	private function log($step) {
		$log = new OrderComponentLog();
		$log->order_component_id = $this->id;
		$log->step = $step;
		$log->user_id = Auth::user()->id;
		$log->save();
	}
	
}